package edu.uoc.tdp.pac4.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.uoc.tdp.pac4.beans.Rol;
import edu.uoc.tdp.pac4.beans.Sex;
import edu.uoc.tdp.pac4.beans.Usuari;

public class UserManager {

	Connection con = null;
	PreparedStatement pst = null;
	ResultSet rs = null;


	public UserManager() {

	}

	private Connection getConnection() throws SQLException {
		Connection conn = null;
		conn = DiskManager.getInstance().getConnection();
		return conn;
	}

	
	public Usuari findUserById(String nif) {
		Usuari usuariFilter = new Usuari();
		usuariFilter.setNIF(nif);
		List<Usuari> users = findUsers(usuariFilter);
		if(users.size()!= 1){
			throw new RuntimeException("user not found");
		}
		return users.get(0);
	}

	public List<Usuari> listUser() {
		List<Usuari> users = findUsers(null);
		return users;
	}

	
	/**
	 * 
	 * @return
	 */
	public List<Usuari> findUsers(Usuari usuariFilter) {
		System.out.println(usuariFilter);
		List<Usuari> usuaris = new ArrayList<Usuari>();
		if(usuariFilter == null)	
			usuariFilter = new Usuari();
		
		try {
			String querystring = ""
					+ "SELECT nif, nom, cognoms, datanaixement, datainactivitat,"
					+ " email, rolusuari, sexeusuari, loginusuari, contrasenya "
					+ "FROM USUARI WHERE '0'='0' ";
			
	querystring += " AND ( '0'='" + ((usuariFilter.getNIF() == null) ? 0 : 1) + "' OR  nif = ? ) ";
	querystring += " AND ( '0'='" + ((usuariFilter.getNombre() == null) ? 0 : 1)+ "' OR  nom = ? ) ";
	querystring += " AND ( '0'='" + ((usuariFilter.getApellidos() == null) ? 0 : 1) + "' OR  cognoms = ? ) ";
	//querystring += " AND ( '0'='" + ((usuariFilter.getDataNaixement() == null) ? 0 : 1) + "' OR  datanaixement = ? ) ";
	querystring += " AND ( '0'='" + ((usuariFilter.getUsuariActiu() == null) ? 0 : 1) + "' OR  datainactivitat " + ( (usuariFilter.isActiu())?  " is null ":" is not null " ) + " ) ";
	//querystring += " AND ( '0'='" + ((usuariFilter.getDataBaixa() == null) ? 0 : 1) + "' OR  datainactivitat " + ( (usuariFilter.isActiu())?  " is null ":" is not null " ) + " ) ";
	querystring += " AND ( '0'='" + ((usuariFilter.getEmail() == null) ? 0 : 1) + "' OR  email = ? ) ";
	querystring += " AND ( '0'='" + ((usuariFilter.getRol() == null) ? 0 : 1) + "' OR  rolusuari = ?::ROL ) ";
	querystring += " AND ( '0'='" + ((usuariFilter.getSexe() == null) ? 0 : 1) + "' OR  sexeusuari = ?::Sexe ) ";
	querystring += " AND ( '0'='" + ((usuariFilter.getLogin() == null) ? 0 : 1) + "' OR  loginusuari = ? ) ";
	//querystring += " AND ( '0'='" + ((usuariFilter.getContrasenya() == null) ? 0 : 1) + "' OR  contrasenya = ? ) ";
			
			System.out.println(querystring);
			
			con = getConnection();
			pst = con.prepareStatement(querystring);
			pst.setString(1, usuariFilter.getNIF());
			pst.setString(2, usuariFilter.getNombre());
			pst.setString(3, usuariFilter.getApellidos());
			//pst.setDate(4, new java.sql.Date(usuariFilter.getNacimiento().getTime()));
			//pst.setDate(5, (usuariFilter.getDataBaixa()== null)? null:new java.sql.Date(usuariFilter.getDataBaixa().getTime()));
			pst.setString(4, usuariFilter.getEmail());
			pst.setString(5, (usuariFilter.getRol()!=null)?usuariFilter.getRol().name():null);
			pst.setString(6, (usuariFilter.getSexe()!=null)?usuariFilter.getSexe().name():null);
			pst.setString(7, usuariFilter.getLogin());
			//pst.setString(10, usuariFilter.getContrasenya());
			
			rs = pst.executeQuery();
			while (rs.next()) {

				Usuari usuari = new Usuari();

				usuari.setNIF(rs.getString(1));
				usuari.setNombre(rs.getString(2));
				usuari.setApellidos(rs.getString(3));
				usuari.setNacimiento(rs.getDate(4));
				usuari.setDataBaixa(rs.getDate(5));
				usuari.setEmail(rs.getString(6));
				usuari.setRol(Rol.valueOf(rs.getString(7)));
				usuari.setSexe(Sex.valueOf(rs.getString(8)));
				usuari.setLogin(rs.getString(9));
				usuari.setContrasenya(rs.getString(10));

				usuaris.add(usuari);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return usuaris;

	}

	public void createUser(Usuari usuari) {

		try {
			con = getConnection();
			String pstQuery = "INSERT INTO Usuari(nif, nom, cognoms, datanaixement, datainactivitat,"
					+ " email, rolusuari, sexeusuari, loginusuari, contrasenya )"
					+ "VALUES(?,?,?,?,?,?,?::ROL,?::Sexe,?,?)";
			pst = con.prepareStatement(pstQuery);
			pst.setString(1, usuari.getNIF());
			pst.setString(2, usuari.getNombre());
			pst.setString(3, usuari.getApellidos());
			pst.setDate(4, new java.sql.Date(usuari.getNacimiento().getTime()));
			pst.setDate(5, (usuari.getDataBaixa()== null)? null:new java.sql.Date(usuari.getDataBaixa().getTime()));
			pst.setString(6, usuari.getEmail());
			pst.setString(7, usuari.getRol().name());
			pst.setString(8, usuari.getSexe().name());
			pst.setString(9, usuari.getLogin());
			pst.setString(10, usuari.getContrasenya());
			pst.executeUpdate();
			
			//org.postgresql.util.PSQLException
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}

	public void updateUser(Usuari usuari) {
		
		try {
			String querystring = "UPDATE USUARI SET nom=?, cognoms=?, datanaixement=?, datainactivitat=?,"
				+ " email=?, rolusuari=?::ROL, sexeusuari=?::Sexe, contrasenya=? "
					+ " WHERE nif=?";

			con = getConnection();
			pst = con.prepareStatement(querystring);
			//pst.setString(1, usuari.getNIF());
			pst.setString(1, usuari.getNombre());
			pst.setString(2, usuari.getApellidos());
			pst.setDate(3, new java.sql.Date(usuari.getNacimiento()!=null?usuari.getNacimiento().getTime():System.currentTimeMillis()));
			pst.setDate(4,(usuari.getDataBaixa()==null)?null: new java.sql.Date(usuari.getDataBaixa().getTime()));
			pst.setString(5, usuari.getEmail());
			pst.setString(6, usuari.getRol().name());
			pst.setString(7, usuari.getSexe().name());
			//pst.setString(9, usuari.getLogin());
			pst.setString(8, usuari.getContrasenya());
			pst.setString(9, usuari.getNIF());

			pst.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	public List<Usuari> findAllTeachers() {
		List<Usuari> teachers = new ArrayList<Usuari>();
		Usuari teacher = null;
		try {
			String querystring = "SELECT  nom, cognoms FROM USUARI WHERE rolusuari='Professor' ";
			con = getConnection();

			pst = con.prepareStatement(querystring);
			rs = pst.executeQuery();
			while (rs.next()) {

				//

				teacher = new Usuari();

				teacher.setNombre(rs.getString(1));
				teacher.setApellidos(rs.getString(2));
				teachers.add(teacher);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return teachers;
	}

	public Usuari findTeacherByNames(String nom, String cognoms) {
		Usuari teacher = null;

		try {

			String querystring = "SELECT  nom, cognoms, nif FROM usuari WHERE nom=? AND cognoms=? ";
			con = getConnection();
			pst = con.prepareStatement(querystring);
			pst.setString(1, nom);
			pst.setString(2, cognoms);
			rs = pst.executeQuery();
			if (rs.next()) {
				teacher = new Usuari();
				teacher.setNombre(rs.getString(1));
				teacher.setApellidos(rs.getString(2));
				teacher.setNIF(rs.getString(3));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return teacher;
	}

	public Usuari findTeacherById(String nif) {
		Usuari teacher = null;

		try {

			String querystring = "SELECT  NIF, nom, cognoms, email, sexeusuari, datanaixement, loginusuari, contrasenya, rolusuari  FROM usuari WHERE nif=? ";
			con = getConnection();
			pst = con.prepareStatement(querystring);
			pst.setString(1, nif);
			rs = pst.executeQuery();
			if (rs.next()) {
				teacher = new Usuari();
				teacher.setNIF(rs.getString(1));
				teacher.setNombre(rs.getString(2));
				teacher.setApellidos(rs.getString(3));
				teacher.setEmail(rs.getString(4));
				// teacher.setSexe(rs.getString(5));
				teacher.setNacimiento(rs.getDate(6));
				teacher.setLogin(rs.getString(7));
				teacher.setContrasenya(rs.getString(8));
				// teacher.setRol(rs.getString(9));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return teacher;
	}

	public Usuari findStudent(String nif) {
		Usuari student = null;

		try {
			String querystring = "SELECT nom, cognoms FROM Usuari WHERE nif=?";
			con = getConnection();
			pst = con.prepareStatement(querystring);
			pst.setString(1, nif);

			rs = pst.executeQuery();
			if (rs.next()) {
				student = new Usuari();
				student.setNombre(rs.getString(1));
				student.setApellidos(rs.getString(2));

			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pst != null)
					pst.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return student;
	}

}
